﻿
using DataService.Common;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;
using DataDriven;
using System.Linq;
using DataService.Core.ServiceDTO;
using System.Threading;
using DataService.Core.Log;

namespace DataService.DataAccessLayer
{
    public class MySql : RemoteDB
    {
        public override DatabaseType DBType => DatabaseType.MySql;




        #region 查询
        protected override string SetTotalRowsSql(string tableName, string tableschema, string schema)
        {
            string sql = $"select table_rows from information_schema.tables where table_schema='{tableschema}' and table_name='{tableName}'";
            return sql;
        }
        #endregion


        #region 创建数据库、创建表


        public override string GetIsExistsTableSql(string databaseName, string schemaName)
        {
            string sql = $"select count(1) from information_schema.tables where table_schema='{databaseName}' and table_name=@Name";
            return sql;
        }
        public override bool CreateDataBaseSchema(string dbFileDir, string databaseName, DbConnection conn)
        {
            //MySQL 默认的编码方式 latin1 (单字节编码) 
            //utf8编码能正常显示中文
            string sql = $"create database {databaseName} character set utf8;";
            List<string> tableSchemaTexts = new List<string>();
            tableSchemaTexts.Add(sql);
            MapperService.ExecuteSchemaSql(conn, null, tableSchemaTexts, ParameterPrefix);
            TraceLog.WriteLine($"【{DBType}】【databaseName】创建完成", "创建架构");
            return true;
        }
        public override List<string> FindAllTableNames(DbConnection conn, DbTransaction tran)
        {

            string sql = $"select table_name from information_schema.tables where table_schema='{conn.Database}'";
            var list = MapperService.QueryInternal<string>(conn, sql, null, tran, null, null, ParameterPrefix).ToList();
            return list;
        }
        public override bool CreatTable(Tables tables, DbConnection conn, DbTransaction tran)
        {
            //mysql每行最大只能存65535个字节。
            //假设是utf-8编码，每个字符占3个字节。varchar存储最大字符数为(65535-2-1)/3=21844。
            //gbk编码 每个字符占2个字节
            //优化时定时任务提前把第二天的表创建完成
            List<ModelColumns> tableShemas = tables.Columns;
            List<ModelIndexs> tableIndexs = tables.Indexs;
            var tableSchemaTexts = CreateTableSql(tableShemas, tableIndexs);
            string str = string.Join("*", tableSchemaTexts);
            TraceLog.WriteLine(str, "脚本", "MySql创建表");
            //此处需要优化
            int count = MapperService.ExecuteSchemaSql(conn, tran, tableSchemaTexts, ParameterPrefix);

            if (count > 0)
            {
                List<string> tempTableNames = new List<string>();
                int hashCode = ConnectionString.GetHashCode();
                if (_tablesFields.ContainsKey(hashCode))
                {
                    tempTableNames = _tablesFields[hashCode];
                    if (!tempTableNames.Any(x => x == tableShemas[0].FullTableName))
                    {
                        tempTableNames.Add(tableShemas[0].FullTableName);
                        _tablesFields[hashCode] = tempTableNames;
                        TraceLog.WriteLine($"创建表结构【CreatTable】：{tableShemas[0].FullTableName}，添加到缓存", $"{DBType}创建表");
                    }

                }
                else
                {
                    tempTableNames.Add(tableShemas[0].FullTableName);
                    _tablesFields[hashCode] = tempTableNames;
                }
            }

            return count > 0;
        }
        private List<string> CreateTableSql(List<ModelColumns> tableSchemas, List<ModelIndexs> tableIndexs)
        {
            List<string> textSchemas = new List<string>();
            var tableNameList = tableSchemas.Select(x => x.FullTableName).Distinct().ToList();
            foreach (var itemTableName in tableNameList)
            {
                var colSchemaList = tableSchemas.Where(x => x.FullTableName == itemTableName).ToList();
                StringBuilder stringBuilder = new StringBuilder();


                string tableName = itemTableName;//分表后缀,前调用分表前处理
                //这里需要按照规则生成表名
                stringBuilder.Append($"CREATE TABLE {tableName}(");

                foreach (var itemCol in colSchemaList)
                {
                    stringBuilder.Append(itemCol.ColName).Append(" ");
                    stringBuilder.Append(GetColType(itemCol)).Append(" ");
                    stringBuilder.Append(GetColIsNull(itemCol)).Append(" ");
                    stringBuilder.Append(DefaultValue(itemCol)).Append(",");
                }
                stringBuilder.Length--;
                stringBuilder.Append(");");
                stringBuilder.Append(" ");
                //一个表只有一个聚集索引，这里默认主键是聚集索引
                //创建约束、创建索引、组合索引、创建联合主键、创建自增字段、
                //创建索引前需要先删除该索引；DROP INDEX 表名.索引名[,…]
                //如果这个表没有主键，就需要查找是否有聚集索引
                //
                //with (drop_existing = on|off)，加上这个的意思是如果这个索引还在表上就drop 掉然后在create 一个新的。特别是在聚集索引上使用这个就可以不会引起非聚集索引的重建
                //UNIQUE 表示创建的索引具有唯一性
                //CLUSTERED 指明创建的索引为聚集索引
                //NONCLUSTERED 指明创建的索引为非聚集索引
                //ASC、DESC 指定特定的索引列的排序方式为升序(默认)或降序
                List<ModelIndexs> itemTableIndexs = tableIndexs.Where(x => x.FullTableName == itemTableName).ToList();
                if (itemTableIndexs != null && itemTableIndexs.Count > 0)
                {
                    StringBuilder indexBuilder = GetIndexScheme(itemTableIndexs, itemTableName);
                    stringBuilder.Append(indexBuilder);
                }
                textSchemas.Add(stringBuilder.ToString());

            }

            return textSchemas;
        }

        protected override string GetColType(ModelColumns colDTO)
        {

            string tempColType = colDTO.ColType.ToLower();
            if (tempColType == "char" || tempColType == "nchar")
            {
                //存储需求 :M 字节，1<=M<=255
                int tempLength = colDTO.ValueLength;
                if (tempLength == -1 || tempLength > 255)
                {
                    return "char(255)";
                }
                return $"char({tempLength})";
            }
            else if (tempColType == "varchar" || tempColType == "nvarchar")
            {
                //存储需求 :L+1字节，在此，L< = M和 1<=M<=65535
                //当varchar存储的字符个数小于或等于255的时候，首部需要一个字节来记录字符的个数。当内容大于255的字符的时候，首部需要2个自己来保存长度
                int tempLength = colDTO.ValueLength;
                if (tempLength == -1 || tempLength > 65532)
                {
                    return $"varchar(65532)";
                }
                return $"varchar({tempLength})";
            }
            else if (tempColType == "binary" || tempColType == "varbinary")
            {
                int tempLength = colDTO.ValueLength;
                if (tempLength == -1)
                {
                    return $"{tempColType}(65532)";
                }
                return $"{tempColType}({tempLength})";
            }
            else if (tempColType == "bit" || tempColType == "tinyint" || tempColType == "smallint" || tempColType == "int" || tempColType == "bigint" || tempColType == "float")
            {
                return tempColType;
            }
            else if (tempColType == "uniqueidentifier")
            {
                //Guid保存的是16字节的二进制
                //varbinary(16)
                return "varchar(36)";
            }
            else if (tempColType == "datetime" || tempColType == "smalldatetime" || tempColType == "date" || tempColType == "time" || tempColType == "timestamp")
            {
                return tempColType;
            }
            else if (tempColType == "smallmoney" || tempColType == "money")
            {
                return tempColType;
            }
            else if (tempColType == "decimal" || tempColType == "numeric" || tempColType == "double" || tempColType == "float")
            {
                int tempLength = colDTO.ValueLength;
                int num = colDTO.ScaleLength;
                return $"{tempColType}({tempLength},{num})";
            }
            else
            {
                return tempColType;
            }
        }
        private string GetColIsNull(ModelColumns colDTO)
        {
            return colDTO.IsNullable ? "NULL" : "NOT NULL";
        }
        private string DefaultValue(ModelColumns colDTO)
        {
            if (string.IsNullOrEmpty(colDTO.DefaultValue))
            {
                return "";
            }
            string dval = colDTO.DefaultValue.Replace("(", "").Replace(")", "");
            return $"default {dval}";
        }

        private StringBuilder GetIndexScheme(List<ModelIndexs> itemTableIndexs, string itemTableName)
        {
            StringBuilder stringBuilder = new StringBuilder();
            var indexNames = itemTableIndexs.Select(x => x.IndexName).Distinct().ToList();
            int index = 0;
            foreach (string itemIndexName in indexNames)
            {
                index++;
                var tempIndexs = itemTableIndexs.Where(x => x.IndexName == itemIndexName).ToList();
                string colNames = string.Join(",", tempIndexs.Select(x => x.ColumnName));
                var itemTableIndexsDTO = tempIndexs.FirstOrDefault();
                //这里认为主键是聚集索引，正常逻辑，主键也应该是聚集索引
                if (itemTableIndexsDTO.PrimaryKey)
                {
                    //在建立聚集索引的时候在多个字段上建立聚集索引是没有任何意义的；因为聚集索引查找是根据建立索引的第一个字段来查找；这里索引索引排序取第一条排序方式
                    //string pkIndex = $"ALTER TABLE {itemTableName} ADD CONSTRAINT {item.IndexName} PRIMARY KEY CLUSTERED({item.ColumnName} {item.Sort}); ";
                    // string pkIndexSql = $"ALTER TABLE {itemTableName} ADD CONSTRAINT {itemTableIndexsDTO.IndexName} PRIMARY KEY({colNames} {itemTableIndexsDTO.Sort}); ";

                    string tempIndexName = $"PK_{itemTableName}_{index}";
                    string pkIndexSql = $" ALTER TABLE {itemTableName} ADD PRIMARY KEY {tempIndexName}({colNames}); ";
                    stringBuilder.Append(pkIndexSql);
                }
                else if (!itemTableIndexsDTO.PrimaryKey && itemTableIndexsDTO.Type_Desc == "CLUSTERED")
                {
                    //非主键的聚集索引
                    // string tempSql = $"CREATE CLUSTERED INDEX {tempIndexName} ON {itemTableName}({colNames} {itemTableIndexsDTO.Sort});";
                    string tempIndexName = $"INDEX_{itemTableName}_{index}";
                    string tempSql = $" ALTER TABLE {itemTableName} ADD INDEX {tempIndexName}({colNames});";
                    stringBuilder.Append(tempSql);
                }
                else if (itemTableIndexsDTO.UQIQUE && !itemTableIndexsDTO.PrimaryKey)
                {
                    string tempIndexName = $"UNIQUE_INDEX_{itemTableName}_{index}";
                    string tempSql = $" ALTER TABLE {itemTableName} ADD UNIQUE INDEX {tempIndexName}({colNames});";
                    stringBuilder.Append(tempSql);
                }
                else
                {
                    string tempIndexName = $"INDEX_{itemTableName}_{index}";
                    string tempSql = $" ALTER TABLE {itemTableName} ADD INDEX {tempIndexName}({colNames});";
                    stringBuilder.Append(tempSql);
                }
            }
            return stringBuilder;
        }


        #endregion
        #region 数据类型

        private string ColumnSchemaSql = "SELECT TABLE_NAME,ORDINAL_POSITION,COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,CHARACTER_OCTET_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE,IS_NULLABLE,COLUMN_DEFAULT,COLUMN_COMMENT from information_schema.`COLUMNS`";
        private string IndexSql = "SELECT IDX.TABLE_NAME,IDX.INDEX_NAME,IDX.COLUMN_NAME,IDX.INDEX_TYPE,IDC.COLUMN_KEY,IDX.NON_UNIQUE from information_schema.STATISTICS as IDX inner join  information_schema.`COLUMNS` as IDC on IDX.TABLE_NAME=IDC.TABLE_NAME and IDX.COLUMN_NAME=IDC.COLUMN_NAME";

        public override Tables FindAllModelTableSchema(List<string> tableNames, DbConnection conn)
        {

            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.AppendLine(ColumnSchemaSql);
            Dictionary<string, object> dic = new Dictionary<string, object>();
            stringBuilder.Append(" WHERE TABLE_NAME IN(");
            for (int i = 0; i < tableNames.Count; i++)
            {
                stringBuilder.Append($"@name{i}").Append(",");
                dic[$"name{i}"] = tableNames[i];
            }
            stringBuilder.Length--;
            stringBuilder.Append(")");

            DataTable data = MapperService.QueryDataTable(conn, stringBuilder.ToString(), dic, null, null, null, ParameterPrefix);
            var columns = MadeColumnsDatas(data);

            StringBuilder indexSB = new StringBuilder();
            indexSB.AppendLine(IndexSql);
            Dictionary<string, object> indexDic = new Dictionary<string, object>();
            indexSB.Append(" WHERE IDX.TABLE_NAME IN(");
            for (int i = 0; i < tableNames.Count; i++)
            {
                indexSB.Append($"@name{i}").Append(",");
                indexDic[$"name{i}"] = tableNames[i];
            }
            indexSB.Length--;
            indexSB.Append(")");

            DataTable indexDatas = MapperService.QueryDataTable(conn, indexSB.ToString(), indexDic, null, null, null, ParameterPrefix);
            var indexs = MadeIndexData(indexDatas);
            Tables tables = new Tables()
            {
                Columns = columns,
                Indexs = indexs
            };
            return tables;
        }

        private List<ModelIndexs> MadeIndexData(DataTable dataTable)
        {
            List<ModelIndexs> indexsList = new List<ModelIndexs>();
            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                string tableName = dataTable.Rows[i]["TABLE_NAME"].ToString();
                string index_NAME = dataTable.Rows[i]["INDEX_NAME"].ToString();
                string column_NAME = dataTable.Rows[i]["COLUMN_NAME"].ToString();
                // string sort = dataTable.Rows[i]["SORT_ORDER"].ToString();
                string IndexType = dataTable.Rows[i]["INDEX_TYPE"].ToString();
                string pkStr = dataTable.Rows[i]["COLUMN_KEY"].ToString().ToUpper();
                bool ispk = pkStr == "PRI";
                string uniqueStr = dataTable.Rows[i]["NON_UNIQUE"].ToString();
                int.TryParse(uniqueStr, out int unique);
                bool isUnique = unique == 0;
                indexsList.Add(new ModelIndexs
                {
                    TableName = tableName,
                    IndexName = index_NAME,
                    ColumnName = column_NAME,
                    PrimaryKey = ispk,
                    UQIQUE = isUnique
                });
            }
            return indexsList;
        }

        public override List<ModelColumns> FindAllColumns(DbConnection conn)
        {
            DataTable data = MapperService.QueryDataTable(conn, ColumnSchemaSql, null, null, null, null, ParameterPrefix);
            List<ModelColumns> list = MadeColumnsDatas(data);
            return list;
        }
        public override List<ModelIndexs> FindAllIndexs(DbConnection conn)
        {
            DataTable data = MapperService.QueryDataTable(conn, IndexSql, null, null, null, null, ParameterPrefix);
            var indexSchemas = MadeIndexData(data);
            return indexSchemas;
        }
        #endregion
    }
}
